﻿using NPOI.POIFS.Crypt.Dsig;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using 日铁.Modle;

namespace 日铁
{
    public partial class BudgetAdjustmentsList : Form
    {
        Class1 one = new Class1();
        public BudgetAdjustmentsList(string LH="")
        {
            InitializeComponent();
            dataGridView1.CellValueChanged+= new DataGridViewCellEventHandler(dataGridView1_CellValueChanged);
            textBox1.Text = LH;
            this.WindowState = FormWindowState.Maximized;
            this.FormClosing += new FormClosingEventHandler(Form1_FormClosing);
            
        }

        private void 保存ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string Fnumber = textBox1.Text;
                string Fstatus = textBox2.Text;
                string Fyear = textBox3.Text;
                string Fctime = dateTimePicker2.Value.ToString("yyyy-MM-dd");
                string Ffounder = textBox4.Text;
                string Fatime = "1900-01-01";
                string Fauditor = textBox5.Text;

                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet where Fnumber='{Fnumber}'", connection, false, null);
                    int row = dataTable.Rows.Count;
                    if (row > 0)
                    {
                        string FID = DBHelper.ExecuteScalar($"select BASID from T_Budget_adjustment_sheet where Fnumber = '{Fnumber}'", connection, false, null).ToString();
                        DataTable dataTables = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet where Fnumber='{Fnumber}'", connection, false, null);
                        if (dataTables.Rows.Count > 0)
                        {
                            foreach(DataRow dataRow in dataTables.Rows)
                            {
                                string insertText = $"DELETE T_Budget_adjustment_sheet_E WHERE FID='{FID}'";
                                int rows = DBHelper.ExecuteNonQuery(insertText, connection, false, null);
                            }

                            int index = 0;
                            foreach (DataGridViewRow item in dataGridView1.Rows)
                            {
                                if (dataGridView1.Rows.Count - 1 > index)
                                {
                                    string temp = item.Cells[0].Value.ToString();
                                    string Fbtype = item.Cells[1].Value.ToString();
                                    string Fmpurchase = item.Cells[2].Value.ToString();
                                    string FNSCH = item.Cells[3].Value.ToString();
                                    string FMAterialID = item.Cells[4].Value.ToString();
                                    string FMname = item.Cells[5].Value.ToString();
                                    string FSAM = item.Cells[6].Value.ToString();
                                    string Funit = item.Cells[7].Value.ToString();

                                    string FIBA = item.Cells[8].Value.ToString();
                                    string FIBQ = item.Cells[9].Value.ToString();


                                    if (FIBA == "" || FIBA == null)
                                    {
                                        FIBA = "0";
                                    }
                                    if (FIBQ == "" || FIBQ == null)
                                    {
                                        FIBQ = "0";
                                    }
                                    if (temp != "" && FMAterialID!="")
                                    {
                                        int ro = DBHelper.ExecuteNonQuery($"INSERT INTO T_Budget_adjustment_sheet_E (FID, Fbtype, Fmpurchase,FNSCH,FMAterialID,FMname,FSAM,Funit,FBAA,FNBA,Fq) VALUES ({FID}, '{Fbtype}', '{Fmpurchase}','{FNSCH}','{FMAterialID}','{FMname}','{FSAM}','{Funit}',{FIBA},{FIBQ},{temp})", connection, false, null);
                                    }
                                    index++;
                                }
                            }

                        }
                    }
                    else
                    {
                        string insertText = $"insert into T_Budget_adjustment_sheet values('{Fnumber}','{Fstatus}','{Fyear}','{Fctime}','{Ffounder}','{Fatime}','{Fauditor}')";
                        int rows = DBHelper.ExecuteNonQuery(insertText, connection, false, null);

                        string FID = DBHelper.ExecuteScalar($"select BASID from T_Budget_adjustment_sheet where Fnumber = '{Fnumber}'", connection, false, null).ToString();
                        int index = 0;
                        foreach (DataGridViewRow item in dataGridView1.Rows)
                        {
                            if (dataGridView1.Rows.Count - 1 > index)
                            {
                                string temp = item.Cells[0].Value.ToString();
                                string Fbtype = item.Cells[1].Value.ToString();
                                string Fmpurchase = item.Cells[2].Value.ToString();
                                string FNSCH = item.Cells[3].Value.ToString();
                                string FMAterialID = item.Cells[4].Value.ToString();
                                string FMname = item.Cells[5].Value.ToString();
                                string FSAM = item.Cells[6].Value.ToString();
                                string Funit = item.Cells[7].Value.ToString();

                                string FIBA = item.Cells[8].Value.ToString();
                                string FIBQ = item.Cells[9].Value.ToString();
                                if (FIBA == "" || FIBA == null)
                                {
                                    FIBA = "0";
                                }
                                if (FIBQ == "" || FIBQ == null)
                                {
                                    FIBQ = "0";
                                }
                                if (temp != "")
                                {
                                    int ro = DBHelper.ExecuteNonQuery($"INSERT INTO T_Budget_adjustment_sheet_E (FID, Fbtype, Fmpurchase,FNSCH,FMAterialID,FMname,FSAM,Funit,FBAA,FNBA,Fq) VALUES ({FID}, '{Fbtype}', '{Fmpurchase}','{FNSCH}','{FMAterialID}','{FMname}','{FSAM}','{Funit}',{FIBA},{FIBQ},{temp})", connection, false, null);
                                }
                                index++;
                            }
                        }
                    }
                    
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            MessageBox.Show("保存成功");
        }

        private void 审核ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string DJBH = textBox1.Text;
                string Fyear = textBox2.Text;
                string Fatime = DateTime.Now.ToString("yyyy-MM-dd");
                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    string insertText = $"update T_Budget_adjustment_sheet set Fstatus='审核'，Fatime = {Fatime} where Fnumber='{DJBH}'";
                    int rows = DBHelper.ExecuteNonQuery(insertText, connection, false, null);

                    if (rows > 0)
                    {
                        foreach (DataGridViewRow obj2 in dataGridView1.Rows)
                        {
                            if(obj2.Cells[3].Value!=null)
                            {
                                string Fbtype = obj2.Cells[0].Value.ToString();
                                string Fmpurchase = obj2.Cells[1].Value.ToString();
                                string FNSCH = obj2.Cells[2].Value.ToString();
                                string FMID = obj2.Cells[3].Value.ToString();
                                string FMname = obj2.Cells[4].Value.ToString();
                                string FSAM = obj2.Cells[5].Value.ToString();
                                string Funit = obj2.Cells[6].Value.ToString();
                                string FRBA = obj2.Cells[7].Value.ToString();
                                string FRBQ = obj2.Cells[8].Value.ToString();

                                DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Annual_budget_list_E where FMAterialID='{FMID}'", connection, false, null);
                                int row = dataTable.Rows.Count;

                                string FID = DBHelper.ExecuteScalar($"select ABIID from T_Annual_budget_list where Fyear='{Fyear}'", connection, false, null).ToString();

                                if (row > 0)
                                {
                                    string updateabl = $"update T_Annual_budget_list_E set FRBA={FRBA} , FRBQ={FRBQ} where FMAterialID='{FMID}' and FID={FID}";
                                    int ro = DBHelper.ExecuteNonQuery(updateabl, connection, false, null);
                                }
                                else
                                {
                                    string insertABLE = $"insert into T_Annual_budget_list_E (FID, Fbtype, Fmpurchase,FNSCH,FMAterialID,FMname,FSAM,Funit,FIBA,FIBQ,FRBA,FRBQ) values('{FID}','{Fbtype}','{Fmpurchase}','{FNSCH}','{FMID}','{FMname}','{FSAM}','{Funit}','{FRBA}','{FRBQ}','{FRBA}','{FRBQ}')";
                                    int ro = DBHelper.ExecuteNonQuery(insertABLE, connection, false, null);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            MessageBox.Show("审核成功,已修改对应年度预算单");
        }

        private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                string DJBH = textBox1.Text;

                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    string picture = DBHelper.ExecuteScalar($"select BASID from T_Budget_adjustment_sheet where WHERE Fnumber='{DJBH}'", connection, false, null).ToString();

                    string DELETEabl = $"DELETE T_Budget_adjustment_sheet WHERE BASID='{picture}'";
                    int rows = DBHelper.ExecuteNonQuery(DELETEabl, connection, false, null);

                    string DELETEable = $"DELETE T_Budget_adjustment_sheet_E WHERE FID='{picture}'";
                    int rows2 = DBHelper.ExecuteNonQuery(DELETEable, connection, false, null);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void BudgetAdjustmentsList_Load(object sender, EventArgs e)
        {
            Listname();
            string DJBH = textBox1.Text;
            if (DJBH == "")
            {
                string year = DateTime.Now.Year.ToString();
                textBox1.Text = GET_DJSL();
                textBox2.Text = "创建";
                textBox3.Text = year;
                dateTimePicker3.Value = new DateTime(1900, 1, 1);

                DataTable dataTable = one.BASE();
                dataTable.Rows.Add("","", "", "", "", "", "", "", "", "");
                dataGridView1.DataSource = dataTable;

            }
            else
            {
                Get_ndysd(DJBH);
            }
        }
        private string GET_DJSL()
        {
            string Now = DateTime.Now.ToString("yyyy-MM-dd");
            SqlConnection connection = DBHelper.CreateConnection();
            DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet", connection, false, null);
            int rows = dataTable.Rows.Count;

            string DHBM = "YSTZD" + rows + 1 + Now;
            return DHBM;
        }
        private void Get_ndysd(string DDBH)
        {
            try
            {
                string DJBH = textBox1.Text;
                int id = 0;
                using (SqlConnection connection = DBHelper.CreateConnection())
                {
                    DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet where Fnumber='{DDBH}'", connection, false, null);
                    if (dataTable.Rows.Count > 0)
                    {
                        foreach (DataRow row in dataTable.Rows)
                        {
                            id = (int)row["BASID"];
                            textBox1.Text = row["Fnumber"].ToString();
                            textBox2.Text = row["Fstatus"].ToString();
                            textBox3.Text = row["Fyear"].ToString();
                            dateTimePicker2.Value = DateTime.Parse(row["Fctime"].ToString());
                            textBox4.Text = row["Ffounder"].ToString();
                            dateTimePicker3.Value = DateTime.Parse(row["Fatime"].ToString());
                            textBox5.Text = row["Fauditor"].ToString();
                        }
                    }
                    if (id != 0)
                    {
                        

                        DataTable dataTable2 = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet_E where FID='{id}'", connection, false, null);
                        DataTable data = one.BASE();
                        int index = 1;
                        foreach (DataRow row2 in dataTable2.Rows)
                        {
                            data.Rows.Add(row2.ItemArray[11].ToString(), row2.ItemArray[2].ToString(), row2.ItemArray[3].ToString(), row2.ItemArray[4].ToString(), row2.ItemArray[5].ToString(), row2.ItemArray[6].ToString(), row2.ItemArray[7].ToString(), row2.ItemArray[8].ToString(), row2.ItemArray[9].ToString(), row2.ItemArray[10].ToString());
                            index++;
                        }
                        dataGridView1.DataSource = data;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            // 检查是否是一个有效的单元格索引  
            if (e.RowIndex >= 0 && e.ColumnIndex == 4)
            {
                // 获取改变后的单元格值  
                var FMAterialID = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
                if (FMAterialID != null)
                {
                    string FMAterialID2 = FMAterialID.ToString();
                    string Fyear = textBox3.Text;
                    if (Fyear != "" && Fyear != null)
                    {
                        // 在这里处理你的逻辑  
                        using (SqlConnection connection = DBHelper.CreateConnection())
                        {
                            string FID = DBHelper.ExecuteScalar($"select ABIID from T_Annual_budget_list where Fyear='{Fyear}'", connection, false, null).ToString();

                            DataTable dataTable2 = DBHelper.ExecuteDataTable($"select '副资材类'as Fbtype ,'赊购' as Fmpurvhase,'副资材类'as FNSCH , a.FNumber,a.FName,a.FFullName,'批' as Funit from t_icitem as a where a.FNumber='{FMAterialID2}'", connection, false, null);
                            int temp = 1;
                            foreach (DataRow dataRow in dataTable2.Rows)
                            {
                                int index = 0;
                                foreach (DataGridViewRow item in dataGridView1.Rows)
                                {
                                    if (index == e.RowIndex)
                                    {
                                        string FNumber = dataRow.ItemArray[3].ToString();
                                        var FRBA = DBHelper.ExecuteScalar($"select FRBA from T_Annual_budget_list_E where FID='{FID}' and FMAterialID = '{FNumber}'", connection, false, null);
                                        var FRBQ = DBHelper.ExecuteScalar($"select FRBQ from T_Annual_budget_list_E where FID='{FID}' and FMAterialID = '{FNumber}'", connection, false, null);
                                        if (FRBA != null)
                                        {
                                            FRBA = FRBA.ToString();
                                        }
                                        else
                                        {
                                            FRBA = "";
                                        }
                                        if (FRBQ != null)
                                        {
                                            FRBQ = FRBQ.ToString();
                                        }
                                        else
                                        {
                                            FRBA = "";
                                        }
                                        item.Cells[0].Value = temp.ToString();
                                        item.Cells[1].Value = dataRow.ItemArray[0].ToString();
                                        item.Cells[2].Value = dataRow.ItemArray[1].ToString();
                                        item.Cells[3].Value = dataRow.ItemArray[2].ToString();
                                        item.Cells[4].Value = dataRow.ItemArray[3].ToString();
                                        item.Cells[5].Value = dataRow.ItemArray[4].ToString();
                                        item.Cells[6].Value = dataRow.ItemArray[5].ToString();
                                        item.Cells[7].Value = dataRow.ItemArray[6].ToString();
                                        item.Cells[8].Value = FRBA;
                                        item.Cells[9].Value = FRBQ;
                                    }
                                    index++;
                                }
                            }
                        }
                    }
                }
                

            }
        }
        public void Listname()
        {
            DataGridViewTextBoxColumn Fq = new DataGridViewTextBoxColumn();
            Fq.HeaderText = "行号";
            Fq.DataPropertyName = "Fq";
            dataGridView1.Columns.Add(Fq);

            DataGridViewTextBoxColumn Fbtype = new DataGridViewTextBoxColumn();
            Fbtype.HeaderText = "预算类型";
            Fbtype.DataPropertyName = "Fbtype";
            dataGridView1.Columns.Add(Fbtype);

            DataGridViewTextBoxColumn Fmpurchase = new DataGridViewTextBoxColumn();
            Fmpurchase.HeaderText = "采购方式";
            Fmpurchase.DataPropertyName = "Fmpurchase";
            dataGridView1.Columns.Add(Fmpurchase);

            DataGridViewTextBoxColumn FNSCH = new DataGridViewTextBoxColumn();
            FNSCH.HeaderText = "NSCH采购类型";
            FNSCH.DataPropertyName = "FNSCH";
            dataGridView1.Columns.Add(FNSCH);

            DataGridViewTextBoxColumn FMAterialID = new DataGridViewTextBoxColumn();
            FMAterialID.HeaderText = "物料代码";
            FMAterialID.DataPropertyName = "FMAterialID";
            dataGridView1.Columns.Add(FMAterialID);

            DataGridViewTextBoxColumn FMname = new DataGridViewTextBoxColumn();
            FMname.HeaderText = "物料名称";
            FMname.DataPropertyName = "FMname";
            dataGridView1.Columns.Add(FMname);

            DataGridViewTextBoxColumn FSAM = new DataGridViewTextBoxColumn();
            FSAM.HeaderText = "规格型号";
            FSAM.DataPropertyName = "FSAM";
            dataGridView1.Columns.Add(FSAM);

            DataGridViewTextBoxColumn Funit = new DataGridViewTextBoxColumn();
            Funit.HeaderText = "单位";
            Funit.DataPropertyName = "Funit";
            dataGridView1.Columns.Add(Funit);

            DataGridViewTextBoxColumn FBAA = new DataGridViewTextBoxColumn();
            FBAA.HeaderText = "年度预算调整金额";
            FBAA.DataPropertyName = "FBAA";
            dataGridView1.Columns.Add(FBAA);

            DataGridViewTextBoxColumn FNBA = new DataGridViewTextBoxColumn();
            FNBA.HeaderText = "年度预算调整数量";
            FNBA.DataPropertyName = "FNBA";
            dataGridView1.Columns.Add(FNBA);


            dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            
            
        }

        private void 删除选中明细ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataTable data = one.BASE();
            foreach (DataGridViewRow row2 in dataGridView1.Rows)
            {
                data.Rows.Add(row2.Cells[0].Value.ToString(), row2.Cells[1].Value.ToString(), row2.Cells[2].Value.ToString(), row2.Cells[3].Value.ToString(), row2.Cells[4].Value.ToString(), row2.Cells[5].Value.ToString(), row2.Cells[6].Value.ToString(), row2.Cells[7].Value.ToString(), row2.Cells[8].Value.ToString(), row2.Cells[9].Value.ToString());
                
            }
            foreach (DataGridViewRow row in dataGridView1.SelectedRows)
            {
                string FQ = row.Cells[0].Value.ToString();
                try
                {
                    string DJBH = textBox1.Text;

                    using (SqlConnection connection = DBHelper.CreateConnection())
                    {
                        DataTable dataTable = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet where Fnumber='{DJBH}'", connection, false, null);
                        int rows = dataTable.Rows.Count;

                        if (rows > 0) {
                            string picture = DBHelper.ExecuteScalar($"select BASID from T_Budget_adjustment_sheet where  Fnumber='{DJBH}'", connection, false, null).ToString();

                            DataTable dataTable1 = DBHelper.ExecuteDataTable($"select * from T_Budget_adjustment_sheet_E where Fq={FQ} and FID={picture} ", connection, false, null);
                            int row1 = dataTable1.Rows.Count;
                            if (row1 > 0)
                            {
                                string DELETEable = $"DELETE T_Budget_adjustment_sheet_E WHERE FQ={FQ} and FID={picture}";
                                int rows2 = DBHelper.ExecuteNonQuery(DELETEable, connection, false, null);
                                foreach (DataRow roww in data.Rows)
                                {
                                    // 检查条件  
                                    if ((roww["Fq"]).ToString() == FQ)
                                    {
                                        // 标记行为删除  
                                        roww.Delete();
                                    }
                                }
                                data.AcceptChanges();
                            }
                            else
                            {
                                foreach (DataRow roww in data.Rows)
                                {
                                    // 检查条件  
                                    if ((roww["Fq"]).ToString() == FQ)
                                    {
                                        // 标记行为删除  
                                        roww.Delete();
                                    }
                                }
                                data.AcceptChanges();
                            }
                        }
                        else
                        {
                            foreach (DataRow roww in data.Rows)
                            {
                                // 检查条件  
                                if ((roww["Fq"]).ToString() == FQ)
                                {
                                    // 标记行为删除  
                                    roww.Delete();
                                }
                            }
                            data.AcceptChanges();
                        }


                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            dataGridView1.DataSource = data;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            // 这里可以添加一些自定义逻辑，比如询问用户是否确定要关闭窗体  
            DialogResult result = MessageBox.Show("你确定要关闭这个窗体吗？", "确认关闭", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (result == DialogResult.No)
            {
                // 如果用户选择“否”，则取消关闭操作  
                e.Cancel = true;
            }
            else
            {
                // 如果用户选择“是”，可以在这里执行一些清理操作  
                // 例如，保存数据、释放资源等  
                // 这里可以添加你的清理代码  
                BudgetAdjustmentSheet longk = new BudgetAdjustmentSheet();
                longk.WindowState = FormWindowState.Normal;
                longk.Show();
            }
        }
    }
}
